﻿using Model;
using MySqlConnector;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class ClubService
    {
        public bool AddClub(Club club)
        {
            string sql = " Insert into club values(@clubid,@clubname,@teacher,@founddate,@intorduction)";
            MySqlParameter[] para = new MySqlParameter[5];
            para[0] = new MySqlParameter("@clubid", club.Clubid);
            para[1] = new MySqlParameter("@clubname", club.Clubname);
            para[2] = new MySqlParameter("@teacher", club.Teachername);
            para[3] = new MySqlParameter("@founddate", club.Founddate);
            para[4] = new MySqlParameter("@intorduction", club.Introduction);
            
            try
            {
                int n = DAL.SQLHelper.ExecuteNonQuery(sql, para);
                if (n > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public ArrayList GetALLClubs(string sql)//查询社团信息
        {
            string ConnectionString = "server=localhost;database=studentclubmis;user=root ;password=123456 ;port=3306";
            MySqlConnection con = null;
            DataSet ds = new DataSet();
            ArrayList memberList = null;
            try
            {
                con = new MySqlConnection(ConnectionString);
                con.Open();
                MySqlCommand cmd = new MySqlCommand(sql, con);
                MySqlDataAdapter da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(ds, "clubs");
                if (ds.Tables["clubs"].Rows.Count > 0)
                {
                    memberList = new ArrayList();
                    for (int i = 0; i < ds.Tables["clubs"].Rows.Count; i++)
                    {
                        Club member1 = new Club();
                        DataRow row = ds.Tables["clubs"].Rows[i];
                        member1.Clubid = int.Parse(row["clubid"].ToString());
                        member1.Clubname = (row["clubname"].ToString());
                        member1.Teachername = (row["teacher"].ToString());
                        member1.Departmentname = row["departmentname"].ToString();
                        member1.Founddate = Convert.ToDateTime(row["founddate"]);
                        member1.Introduction = row["introduction"].ToString();
                        memberList.Add(member1);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
            return memberList;
        }
        
        public ArrayList GetALLClub(string sql)//查询社团信息2
        {
            string ConnectionString = "server=localhost;database=studentclubmis;user=root ;password=123456 ;port=3306";
            MySqlConnection con = null;
            DataSet ds = new DataSet();
            ArrayList memberList = null;
            try
            {
                con = new MySqlConnection(ConnectionString);
                con.Open();
                MySqlCommand cmd = new MySqlCommand(sql, con);
                MySqlDataAdapter da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(ds, "clubs");
                if (ds.Tables["clubs"].Rows.Count > 0)
                {
                    memberList = new ArrayList();
                    for (int i = 0; i < ds.Tables["clubs"].Rows.Count; i++)
                    {
                        Club member1 = new Club();
                        DataRow row = ds.Tables["clubs"].Rows[i];
                        member1.Clubid = int.Parse(row["clubid"].ToString());
                        member1.Clubname = (row["clubname"].ToString());
                        member1.Teachername = row["teacher"].ToString();
                        member1.Founddate = Convert.ToDateTime(row["founddate"]);
                        member1.Introduction = row["introduction"].ToString();
                        memberList.Add(member1);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                con.Close();
            }
            return memberList;
        }

        public bool UptClub(Club club)
        {
            string sql = " Update club set clubid=@clubid,clubname=@clubname,teacher=@teacher,founddate=@founddate,introduction=@introduction where clubname=@clubname";
            MySqlParameter[] para = new MySqlParameter[5];
            para[0] = new MySqlParameter("@clubid", club.Clubid);
            para[1] = new MySqlParameter("@clubname", club.Clubname);
            para[2] = new MySqlParameter("@teacher", club.Teachername);
            para[3] = new MySqlParameter("@founddate", club.Founddate);
            para[4] = new MySqlParameter("@introduction", club.Introduction);
            try
            {
                int n = SQLHelper.ExecuteNonQuery(sql, para);
                if (n > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public bool DelClub(Club club)
        {
            string sql = "delete from club where clubname=@clubname";
            MySqlParameter[] para = new MySqlParameter[1];
            para[0] = new MySqlParameter("@clubname", club.Clubname);
            try
            {
                int n = DAL.SQLHelper.ExecuteNonQuery(sql, para);
                if (n > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
    }
}
